package com.hucsmn.stumansys.dao.impl;

import com.hucsmn.stumansys.dao.StudentDao;
import com.hucsmn.stumansys.entity.Admin;
import com.hucsmn.stumansys.entity.Student;
import com.hucsmn.stumansys.utils.ConvertUtils;
import com.hucsmn.stumansys.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.SQLException;
import java.util.List;

/**
 * StudentDao 的 MySQL 实现。
 * @author hucsmn
 */
public class StudentDaoImpl implements StudentDao {
    private static final Logger logger = LogManager.getLogger(StudentDaoImpl.class);

    @Override
    public boolean addStudent(Student student) {
        boolean success = false;
        String sql = "INSERT INTO student (card, name, gender, birth, email, remark) " +
                "VALUES (?, ?, ?, STR_TO_DATE(?, '%Y-%m-%d %T'), ?, ?)";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            Long maybeId = runner.insert(sql, new ScalarHandler<>(),
                    student.getCard(), student.getName(), student.isGender(),
                    student.getBirth(), student.getEmail(), student.getRemark());
            if (maybeId != null && maybeId > 0 && maybeId <= Integer.MAX_VALUE) {
                success = true;
                student.setId((int) (long) maybeId);
            } else {
                logger.error("addStudent: invalid generated id " + maybeId);
            }
        } catch (SQLException e) {
            logger.error("addStudent", e);
        }
        return success;
    }

    @Override
    public boolean removeStudent(int id) {
        boolean success = false;
        String sql = "DELETE FROM student WHERE id = ?";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            int affected = runner.update(sql, id);
            success = affected == 1;
            if (!success) {
                logger.error("removeStudent: affected " + affected + " rows");
            }
        } catch (SQLException e) {
            logger.error("removeStudent", e);
        }
        return success;
    }

    @Override
    public boolean removeStudents(int[] ids) {
        if (ids.length == 0) {
            return true;
        }

        boolean success = false;
        String sql = "DELETE FROM student WHERE id IN (" + ConvertUtils.idListToString(ids) + ")";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            int affected = runner.update(sql);
            success = affected == ids.length;
            if (!success) {
                logger.error("removeStudents: affected " + affected + " / " + ids.length + " rows");
            }
        } catch (SQLException e) {
            logger.error("removeStudents", e);
        }
        return success;
    }

    @Override
    public boolean setStudent(Student student) {
        boolean success = false;
        String sql = "UPDATE student SET card = ?, name = ?, gender = ?, " +
                "birth = STR_TO_DATE(?, '%Y-%m-%d %T'), email = ?, remark = ? WHERE id = ?";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            int affected = runner.update(sql, student.getCard(), student.getName(), student.isGender(),
                    student.getBirth(), student.getEmail(), student.getRemark(), student.getId());
            success = affected == 1;
            if (!success) {
                logger.error("setStudent: affected " + affected + " rows");
            }
        } catch (SQLException e) {
            logger.error("setStudent", e);
        }
        return success;
    }

    @Override
    public Student getStudentById(int id) {
        Student student = null;
        String sql = "SELECT id, card, name, gender, birth, email, remark FROM student WHERE id = ?";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            student = runner.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException e) {
            logger.error("getStudentById", e);
        }
        return student;
    }

    @Override
    public Student getStudentByCard(String card) {
        Student student = null;
        String sql = "SELECT id, card, name, gender, birth, email, remark FROM student WHERE card = ?";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            student = runner.query(sql, new BeanHandler<>(Student.class), card);
        } catch (SQLException e) {
            logger.error("getStudentByCard", e);
        }
        return student;
    }

    @Override
    public long countStudents() {
        long count = -1;
        String sql = "SELECT COUNT(*) FROM student";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            Long maybeCount = runner.query(sql, new ScalarHandler<>());
            if (maybeCount != null && maybeCount >= 0 && maybeCount <= Integer.MAX_VALUE) {
                count = maybeCount;
            } else {
                logger.error("countStudents: invalid count " + maybeCount);
            }
        } catch (SQLException e) {
            logger.error("countStudents", e);
        }
        return count;
    }

    @Override
    public List<Student> getStudents(long since, int limit) {
        List<Student> students = null;
        String sql = "SELECT id, card, name, gender, birth, email, remark FROM student ORDER BY id LIMIT ?, ?";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            students = runner.query(sql, new BeanListHandler<>(Student.class), since, limit);
        } catch (SQLException e) {
            logger.error("getStudents", e);
        }
        return students;
    }

    @Override
    public long countStudentsNameContains(String contains) {
        long count = -1;
        String sql = "SELECT COUNT(*) FROM student WHERE name LIKE ?";
        String pattern = "%" + DbUtils.escapeLikePatternUseExclamation(contains) + "%";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            Long maybeCount = runner.query(sql, new ScalarHandler<>(), pattern);
            if (maybeCount != null && maybeCount >= 0 && maybeCount <= Integer.MAX_VALUE) {
                count = maybeCount;
            } else {
                logger.error("countStudentsNameContains: invalid count " + maybeCount);
            }
        } catch (SQLException e) {
            logger.error("countStudentsByName", e);
        }
        return count;
    }

    @Override
    public List<Student> getStudentsNameContains(String contains, long since, int limit) {
        List<Student> students = null;
        String sql = "SELECT id, card, name, gender, birth, email, remark FROM student " +
                "WHERE name LIKE ? ORDER BY id LIMIT ?, ?";
        String pattern = "%" + DbUtils.escapeLikePatternUseExclamation(contains) + "%";
        try {
            QueryRunner runner = new QueryRunner(DbUtils.getDataSource());
            students = runner.query(sql, new BeanListHandler<>(Student.class), pattern, since, limit);
        } catch (SQLException e) {
            logger.error("getStudentsByName", e);
        }
        return students;
    }
}
